﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Utility;
using System.Linq;

namespace DataAccess
{
    public abstract class BaseDA
    {
        //  IList<Database> dbList = new List<Database>(EnterpriseLibraryContainer.Current.GetAllInstances<Database>());
        IList<Database> dbList = null;
        public int TimeOut = 60 * 0;
        DbTransaction _trans = null;
        DbConnection _conn = null;

        protected BaseDA()
        {
            GetAllDatabase();
        }

        /// <summary>
        /// 加入事务处理
        /// </summary>
        public DbTransaction JoinTrans
        {
            get { return _trans; }
            set { _trans = value; }
        }

        /// <summary>
        /// 初始化事务
        /// </summary>
        private void InitTransaction(IDictionary<string, object> formParams)
        {
            if (_trans == null)
            {
                _conn = GetCurrentDB(formParams).CreateConnection();
                _conn.Open();
                _trans = _conn.BeginTransaction();
            }
        }

        /// <summary>
        /// 得到可用的DbTransaction
        /// </summary>
        /// <returns></returns>
        private DbTransaction GetAvailableByDbTransaction()
        {
            if (JoinTrans == null)
            {
                return null;
            }

            if (JoinTrans.Connection == null || JoinTrans.Connection.State != ConnectionState.Open)
            {
                return null;
            }

            return JoinTrans;
        }

        /// <summary>
        /// 执行存储过程，返回数据集
        /// </summary>
        /// <param name="ProcName"></param>
        /// <returns></returns>
        public DataSet Query(string procName, Database currentDB)
        {
            try
            {
                DataSet result = null;
                if (GetAvailableByDbTransaction() == null)
                {
                    result = currentDB.ExecuteDataSet(CommandType.Text, procName);
                }
                else
                {
                    result = currentDB.ExecuteDataSet(JoinTrans, CommandType.Text, procName);
                }
                return result;
            }
            catch (Exception ex)
            {
                Logger.Instance.WriteEventLog("DQL操作", "Query", procName, ex, "", LoggerType.sql);
                throw ex;
            }
        }

        /// <summary>
        /// 执行存储过程，返回数据集
        /// </summary>
        /// <param name="ProcName"></param>
        /// <param name="QueryParams"></param>
        /// <returns></returns>
        public DataSet Query(string procName, IDictionary<string, object> queryParams, bool isLogin = true)
        {
            try
            {
                Database currentDB = this.GetCurrentDB(queryParams, isLogin);
                DbCommand cmd = this.BuildCommand(currentDB, procName, queryParams);

                DataSet dset = ToDataSet(currentDB,cmd,queryParams);
                return dset;
                
            }
            catch (Exception ex)
            {
                Logger.Instance.WriteEventLog("DQL操作", "Query", (Dictionary<string, object>)queryParams,ex, "", LoggerType.sql);
                throw ex;
            }
        }

        public DataTable QueryRow(string procName, IDictionary<string, object> queryParams)
        {
            Database currentDB = this.GetCurrentDB(queryParams);
            using (DbConnection conn = currentDB.CreateConnection())
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                try
                {

                    DbCommand cmd = this.BuildCommand(currentDB, procName, queryParams);

                    DbDataAdapter adpt = currentDB.GetDataAdapter();
                    adpt.SelectCommand = cmd;
                    if (GetAvailableByDbTransaction() != null)
                    {
                        adpt.SelectCommand.Transaction = JoinTrans;
                        adpt.SelectCommand.Connection = JoinTrans.Connection;
                    }
                    adpt.SelectCommand.Connection = conn;
                    DataTable dtbl = new DataTable();
                    adpt.Fill(0, 1, dtbl);
                    return dtbl;

                }
                catch (Exception ex)
                {
                    Logger.Instance.WriteEventLog("DQL操作", "QueryRow", (Dictionary<string, object>)queryParams, ex, "", LoggerType.sql);
                    throw ex;
                }
            }

        }

        /// <summary>
        /// 执行存储过程Text by parameters，返回数据集
        /// </summary>
        /// <param name="ProcName"></param>
        /// <returns></returns>
        public DataSet QueryTextByParams(string sqlText, IDictionary<string, object> queryParams)
        {
            try
            {
                
                Database currentDB = this.GetCurrentDB(queryParams);
                DbCommand cmd = this.BuildCommandByDict(currentDB, sqlText, queryParams);
                DataSet result =ToDataSet(currentDB,cmd,queryParams);
                return result;
            }
            catch (Exception ex)
            {
                Logger.Instance.WriteEventLog("DQL操作", "QueryTextByParams", (Dictionary<string, object>)queryParams,ex, "", LoggerType.sql);
                throw ex;
            }
        }

        /// <summary>
        /// 执行存储过程，不返回输出参数
        /// </summary>
        /// <param name="ProcName"></param>
        /// <param name="ExcuteParams"></param>
        /// <returns></returns>
        public int Excute(string procName, IDictionary<string, object> excuteParams)
        {
            try
            {
                Database currentDB = this.GetCurrentDB(excuteParams);
                DbCommand cmd = this.BuildCommand(currentDB, procName, excuteParams);

                if (GetAvailableByDbTransaction() != null)
                    return currentDB.ExecuteNonQuery(cmd, JoinTrans);
                return currentDB.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 事物提交
        /// </summary>
        /// <param name="ProcName"></param>
        /// <param name="ExcuteParams"></param>
        /// <returns></returns>
        public bool TranCommit()
        {
            try
            {
                if (_trans != null)
                {
                    _trans.Commit();
                    _trans = null;
                }

                return true;
            }
            catch (Exception ex)
            {
                if (_trans != null)
                {
                    _trans.Rollback();
                    _trans = null;
                }
                throw ex;
            }
        }

        /// <summary>
        /// 事物回滚
        /// </summary>
        /// <param name="ProcName"></param>
        /// <param name="ExcuteParams"></param>
        /// <returns></returns>
        public bool TranRollback()
        {
            try
            {
                if (_trans != null)
                {
                    _trans.Rollback();
                    _trans = null;
                }

                return true;
            }
            catch (Exception ex)
            {
                if (_trans != null)
                {
                    _trans.Rollback();
                    _trans = null;
                }
                throw ex;
            }
        }

        /// <summary>
        /// 执行存储过程，返回outputParams
        /// </summary>
        /// <param name="ProcName"></param>
        /// <param name="ExcuteParams"></param>
        /// <returns></returns>
        public IDictionary<string, object> ExcuteOutput(string procName, IDictionary<string, object> excuteParams)
        {
            IDictionary<string, object> outputParams = null;
            try
            {
                Database currentDB = this.GetCurrentDB(excuteParams);
                DbCommand cmd = this.BuildCommand(currentDB, procName, excuteParams);

                if (GetAvailableByDbTransaction() != null)
                {
                    currentDB.ExecuteNonQuery(cmd, JoinTrans);
                }
                else
                {
                    currentDB.ExecuteNonQuery(cmd);
                }

                outputParams = this.GetOutput(currentDB, procName, cmd);
                foreach (string key in outputParams.Keys)
                {
                    excuteParams[key] = outputParams[key];
                }
                Logger.Instance.WriteEventLog("DML操作", "ExcuteOutput", (Dictionary<string, object>)excuteParams,  "", LoggerType.sql);
                Logger.Instance.WriteEventLog("DML操作", "ExcuteOutput-返回", (Dictionary<string, object>)outputParams, "", LoggerType.sql);
                return outputParams;
            }
            catch(Exception ex)
            {
                Logger.Instance.WriteEventLog("DML操作 异常", "ExcuteOutput", (Dictionary<string, object>)excuteParams,ex, "", LoggerType.sql);
                if (GetAvailableByDbTransaction() != null)
                {
                    throw ex;
                }
                else
                {
                    return new Utity.ParamterSet().SetValueChain("RETURN_CODE",999).SetValueChain("RETURN_MESSAGE",ex.Message).GetValues();
                }
            }
        }

        public IDictionary<string, object> ExcuteTextOutputDulpex(string sqlText, IDictionary<string, object> excuteParams, out DataSet outputDateSet)
        {
            IDictionary<string, object> outputParams = null;
            outputDateSet = null;
            try
            {
                Database currentDB = this.GetCurrentDB(excuteParams);
                DbCommand cmd = this.BuildCommandByDict(currentDB, sqlText, excuteParams);
                outputDateSet = ToDataSet(currentDB, cmd, excuteParams);

                outputParams = this.GetOutput(currentDB, sqlText, cmd);
                foreach (string key in outputParams.Keys)
                {
                    excuteParams[key] = outputParams[key];
                }
                Logger.Instance.WriteEventLog("DML操作", "ExcuteOutputDulpex", (Dictionary<string, object>)excuteParams, "", LoggerType.sql);
                Logger.Instance.WriteEventLog("DML操作", "ExcuteOutputDulpex-返回", (Dictionary<string, object>)outputParams, "", LoggerType.sql);
                return outputParams;
            }
            catch (Exception ex)
            {
                Logger.Instance.WriteEventLog("DML操作 异常", "ExcuteOutputDulpex", (Dictionary<string, object>)excuteParams, ex, "", LoggerType.sql);
                if (GetAvailableByDbTransaction() != null)
                {
                    throw ex;
                }
                else
                {

                    return new Utity.ParamterSet().SetValueChain("RETURN_CODE", 999).SetValueChain("RETURN_MESSAGE", ex.Message).GetValues();
                }
            }
        }

        public IDictionary<string, object> ExcuteOutputDulpex(string procName, IDictionary<string, object> excuteParams, out DataSet outputDateSet)
        {
            IDictionary<string, object> outputParams = null;
            outputDateSet = null;
            try
            {
                Database currentDB = this.GetCurrentDB(excuteParams);
                DbCommand cmd = this.BuildCommand(currentDB, procName, excuteParams);
                //if (GetAvailableByDbTransaction() != null)
                //{
                //    outputDateSet = currentDB.ExecuteDataSet(cmd, JoinTrans);
                //}
                //else
                //{
                //    outputDateSet = currentDB.ExecuteDataSet(cmd);
                //}
                outputDateSet= ToDataSet(currentDB, cmd, excuteParams);

                outputParams = this.GetOutput(currentDB, procName, cmd);
                foreach (string key in outputParams.Keys)
                {
                    excuteParams[key] = outputParams[key];
                }
                Logger.Instance.WriteEventLog("DML操作", "ExcuteOutputDulpex", (Dictionary<string, object>)excuteParams, "", LoggerType.sql);
                Logger.Instance.WriteEventLog("DML操作", "ExcuteOutputDulpex-返回", (Dictionary<string, object>)outputParams, "", LoggerType.sql);
                return outputParams;
            }
            catch(Exception ex)
            {
                Logger.Instance.WriteEventLog("DML操作 异常", "ExcuteOutputDulpex", (Dictionary<string, object>)excuteParams, ex, "", LoggerType.sql);
                if (GetAvailableByDbTransaction() != null)
                {
                    throw ex;
                }
                else
                {
                    
                    return new Utity.ParamterSet().SetValueChain("RETURN_CODE", 999).SetValueChain("RETURN_MESSAGE", ex.Message).GetValues();
                }
            }  
        }

        DataSet ToDataSet(Database currentDB,DbCommand cmd, IDictionary<string, object> formParams)
        {
            using (DbConnection conn = currentDB.CreateConnection())
            {
                try
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    DataSet outputDateSet = new DataSet();
                    DbDataAdapter adpt = currentDB.GetDataAdapter();
                    adpt.SelectCommand = cmd;
                    if (GetAvailableByDbTransaction() != null)
                    {
                        adpt.SelectCommand.Transaction = JoinTrans;
                        adpt.SelectCommand.Connection = JoinTrans.Connection;
                    }
                    else
                    {
                        adpt.SelectCommand.Connection = conn;
                    }
                    if (formParams.ContainsKey("page") && formParams.ContainsKey("rows") && !formParams.ContainsKey("adpter_fill_all"))
                    {
                        int page = Convert.ToInt32(formParams["page"]) - 1;
                        if (page < 0) page = 0;
                        int rows = Convert.ToInt32(formParams["rows"]);
                        int startIndex = page * rows;
                        int maxIndex = rows;

                        int effectRows = adpt.Fill(outputDateSet, startIndex, maxIndex, "Table");

                        formParams.Remove("page");
                        formParams.Remove("rows");
                    }
                    else
                    {
                        adpt.Fill(outputDateSet);
                    }
                    return outputDateSet;
                }
                catch( Exception ex )
                {
                    Logger.Instance.WriteEventLog("DQL操作", "ToDataSet", (Dictionary<string, object>)formParams, ex, "", LoggerType.sql);
                    throw ex;
                }
            }
        }

        public bool Excute(string procName, IDictionary<string, object> excuteParams, ref string msg)
        {
            try
            {
                Database currentDB = this.GetCurrentDB(excuteParams);
                DbCommand cmd = this.BuildCommand(currentDB, procName, excuteParams);

                if (cmd.Parameters.Contains("@pRETURN_MESSAGE"))
                    cmd.Parameters["@pRETURN_MESSAGE"].Direction = ParameterDirection.InputOutput;

                if (GetAvailableByDbTransaction() != null)
                {
                    currentDB.ExecuteNonQuery(cmd, JoinTrans);
                }
                else
                {
                    currentDB.ExecuteNonQuery(cmd);
                }

                string returnCode = currentDB.GetParameterValue(cmd, "@RETURN_VALUE").ToString();
                msg = currentDB.GetParameterValue(cmd, "@pRETURN_MESSAGE") as string;

                if (returnCode != "0")
                    return false;

                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行存储过程，返回输出参数
        /// </summary>
        /// <param name="ProcName"></param>
        /// <param name="ExcuteParams"></param>
        /// <param name="OutputParamName"></param>
        /// <returns></returns>
        public bool Excute(string procName, IDictionary<string, object> excuteParams, ref string msg, ref object outputParam)
        {
            try
            {
                Database currentDB = this.GetCurrentDB(excuteParams);
                DbCommand cmd = this.BuildCommand(currentDB, procName, excuteParams);

                string paramName = "@p" + outputParam.ToString();
                if (cmd.Parameters.Contains(paramName))
                    cmd.Parameters[paramName].Direction = ParameterDirection.InputOutput;

                if (GetAvailableByDbTransaction() != null)
                {
                    currentDB.ExecuteNonQuery(cmd, JoinTrans);
                }
                else
                {
                    currentDB.ExecuteNonQuery(cmd);
                }

                string returnCode = currentDB.GetParameterValue(cmd, "@RETURN_VALUE").ToString();
                msg = currentDB.GetParameterValue(cmd, "@pRETURN_MESSAGE") as string;

                if (returnCode != "0")
                    return false;

                outputParam = currentDB.GetParameterValue(cmd, paramName);

                return true;
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取存储过程参数
        /// </summary>
        /// <param name="ProcName"></param>
        /// <returns></returns>
        private DataTable GetParamList(Database currentDB, string procName)
        {
            DbCommand cmd = currentDB.GetStoredProcCommand(Constant.GET_PROC_PARAM_LIST);
            cmd.CommandTimeout = 2;
            cmd.Parameters.Add(new SqlParameter("@procedure_name", procName));

            DataSet ds = currentDB.ExecuteDataSet(cmd);

            return ds.Tables[0];
        }

        /// <summary>
        /// 创建Command
        /// </summary>
        /// <param name="ProcName"></param>
        /// <param name="QueryParam"></param>
        /// <returns></returns>
        private DbCommand BuildCommand(Database currentDB, string procName, IDictionary<string, object> formParams)
        {
            DbCommand cmd = currentDB.GetStoredProcCommand(procName);

            cmd.CommandTimeout = TimeOut;

            if (formParams == null)
                return cmd;

            DataTable procParamList = this.GetParamList(currentDB, procName);
            SqlParameter param;
            
            if (procParamList.Select("[COLUMN_NAME]='@pquery_start_index' OR [COLUMN_NAME]='@pquery_end_index'").Length == 2
                  && formParams.ContainsKey("page") && formParams.ContainsKey("rows")
                )
            {
                int page = Convert.ToInt32(formParams["page"]) - 1;
                int rows = Convert.ToInt32(formParams["rows"]);
                int startIndex = page * rows + 1;
                int endIndex = page * rows + rows;
                param =  new SqlParameter();
                param.ParameterName = "@pquery_start_index";
                param.Value = startIndex;
                param.SqlDbType = SqlDbType.Int;
                cmd.Parameters.Add(param);

                param = new SqlParameter();
                param.ParameterName = "@pquery_end_index";
                param.Value = endIndex;
                param.SqlDbType = SqlDbType.Int;
                cmd.Parameters.Add(param);
                formParams.Remove("page");
                formParams.Remove("rows");
            }

            foreach (DataRow row in procParamList.Rows)
            {
                string columnName = row["COLUMN_NAME"] as string;
                string paramName = columnName.Replace("@p", "");
                string size = row["LENGTH"].ToString();
                string typeName = row["TYPE_NAME"] as string;

                string columnType = row["COLUMN_TYPE"].ToString();
                //columnName.Equals("@RETURN_VALUE") || 
                if (columnName.Equals("@pquery_start_index") || columnName.Equals("@pquery_end_index"))
                    continue;

                try
                {
                    param = new SqlParameter();

                    param.ParameterName = columnName;
                    if (formParams.ContainsKey(paramName))
                        if (typeName.IndexOf("char") >= 0 || typeName.IndexOf("text") >= 0)
                            param.Value = formParams[paramName];
                        else
                            param.Value = string.IsNullOrEmpty(formParams[paramName].ToString()) ? null : formParams[paramName];
                    else
                        param.Value = null;

                    param.SqlDbType = SqlTypeName2SqlDbType(typeName);

                    param.Size = int.Parse(size);

                    if (columnType=="2")
                    {
                        param.Direction = ParameterDirection.InputOutput;
                    }

                    if (columnType == "5")
                    {
                        param.Direction = ParameterDirection.ReturnValue;
                    }

                    cmd.Parameters.Add(param);
                }
                catch
                {
                    continue;
                }
            }

            return cmd;
        }

        /// <summary>
        /// 得到Command中output值
        /// </summary>
        /// <param name="currentDB"></param>
        /// <param name="ProcName"></param>
        /// <param name="formParams"></param>
        /// <returns></returns>
        private IDictionary<string, object> GetOutput(Database currentDB, string procName, DbCommand cmd, string strReplace = "@p")
        {
            IDictionary<string, object> outputParams = new Dictionary<string, object>();

            //  DataTable procParamList = this.GetParamList(currentDB, procName);

            foreach (SqlParameter param in cmd.Parameters)
            {
                if (param.Direction == ParameterDirection.InputOutput)
                {
                    outputParams.Add(param.ParameterName.Replace(strReplace, ""), param.Value);
                }
            }

            return outputParams;
        }

        /// <summary>
        /// 数据类型名转换成SqlDbType
        /// </summary>
        /// <param name="sqlTypeName"></param>
        /// <returns></returns>
        private SqlDbType SqlTypeName2SqlDbType(string sqlTypeName)
        {
            SqlDbType dbType = SqlDbType.Variant;
            //默认为Object
            switch (sqlTypeName)
            {
                case "int":
                    dbType = SqlDbType.Int;
                    break;
                case "varchar":
                    dbType = SqlDbType.VarChar;
                    dbType = SqlDbType.NVarChar;
                    break;
                case "bit":
                    dbType = SqlDbType.Bit;
                    break;
                case "datetime":
                    dbType = SqlDbType.DateTime;
                    break;
                case "decimal":
                    dbType = SqlDbType.Decimal;
                    break;
                case "float":
                    dbType = SqlDbType.Float;
                    break;
                case "image":
                    dbType = SqlDbType.Image;
                    break;
                case "money":
                    dbType = SqlDbType.Money;
                    break;
                case "ntext":
                    dbType = SqlDbType.NText;
                    break;
                case "nvarchar":
                    dbType = SqlDbType.NVarChar;
                    break;
                case "smalldatetime":
                    dbType = SqlDbType.SmallDateTime;
                    break;
                case "smallint":
                    dbType = SqlDbType.SmallInt;
                    break;
                case "text":
                    dbType = SqlDbType.Text;
                    break;
                case "bigint":
                    dbType = SqlDbType.BigInt;
                    break;
                case "binary":
                    dbType = SqlDbType.Binary;
                    break;
                case "char":
                    dbType = SqlDbType.Char;
                    break;
                case "nchar":
                    dbType = SqlDbType.NChar;
                    break;
                case "numeric":
                    dbType = SqlDbType.Decimal;
                    break;
                case "real":
                    dbType = SqlDbType.Real;
                    break;
                case "smallmoney":
                    dbType = SqlDbType.SmallMoney;
                    break;
                case "sql_variant":
                    dbType = SqlDbType.Variant;
                    break;
                case "timestamp":
                    dbType = SqlDbType.Timestamp;
                    break;
                case "tinyint":
                    dbType = SqlDbType.TinyInt;
                    break;
                case "uniqueidentifier":
                    dbType = SqlDbType.UniqueIdentifier;
                    break;
                case "varbinary":
                    dbType = SqlDbType.VarBinary;
                    break;
                case "xml":
                    dbType = SqlDbType.Xml;
                    break;
            }

            return dbType;
        }

        /// <summary>
        /// 根据数据库名称获取当前数据
        /// </summary>
        /// <param name="formParams"></param>
        /// <returns></returns>
        public Database GetCurrentDB(IDictionary<string, object> formParams, bool isLogin = true)
        {
            if (!formParams.ContainsKey("DB_NAME"))
                throw new Exception("参数中没有DB_NAME项");

            string DBName = formParams["DB_NAME"] as string;
            if (string.IsNullOrEmpty(DBName))
                throw new Exception("参数DB_NAME为空");

            Database currentDB = null;

            foreach (Database db in dbList)
            {
                if (db.ConnectionString.ToUpper().Contains(DBName))
                {
                    currentDB = db;
                    break;
                }
            }

            if (currentDB == null)
                throw new Exception("没有找到" + DBName + "数据库");

            //if (isLogin)
            //{
            //    if (formParams.ContainsKey("EHUSER"))
            //    {
            //        if (string.IsNullOrEmpty(formParams["EHUSER"] as string))
            //            throw new Exception("操作用户丢失，请重新登陆");
            //    }
            //    else
            //    {
            //        throw new Exception("非法操作，请重新登陆");
            //    }
            //}

            return currentDB;
        }

        /// <summary>
        /// 根据数据库名称获取当前数据
        /// </summary>
        /// <param name="formParams"></param>
        /// <returns></returns>
        protected Database GetCurrentDB(string strDataBaseName)
        {
            if (string.IsNullOrEmpty(strDataBaseName))
                throw new Exception("参数DB_NAME为空");

            Database currentDB = null;

            foreach (Database db in dbList)
            {
                if (db.ConnectionString.ToUpper().Contains(strDataBaseName))
                {
                    currentDB = db;

                    break;
                }
            }

            if (currentDB == null)
                throw new Exception("没有找到" + strDataBaseName + "数据库");

            return currentDB;
        }

        public void GetAllDatabase()
        {
            if (dbList == null)
            {
                dbList = new List<Database>();

                foreach (ConnectionStringSettings s in ConfigurationManager.ConnectionStrings)
                {
                    if (s.ConnectionString == "" || s.ProviderName == "")
                        continue;
                    DbProviderFactory f = DbProviderFactories.GetFactory(s.ProviderName);
                    Database db;

                    if (s.ConnectionString.ToLower().IndexOf("uid") == -1)
                    {
                        db = new GenericDatabase(s.ConnectionString + "Uid=sa;password=Malocl321;", f);
                    }
                    else {
                        db = new GenericDatabase(s.ConnectionString, f);
                    }
                    dbList.Add(db);
                }
                //  dbList = new List<Database>(EnterpriseLibraryContainer.Current.GetAllInstances<Database>());
            }
        }


        #region 20170311 julian New
        /// <summary>
        /// 根据Dictionary 生成cmd
        /// </summary>
        /// <param name="currentDB"></param>
        /// <param name="sqlText"></param>
        /// <param name="formParams"></param>
        /// <returns></returns>
        private DbCommand BuildCommandByDict(Database currentDB, string sqlText, IDictionary<string, object> formParams)
        {
            DbCommand cmd = currentDB.GetSqlStringCommand(sqlText);
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = TimeOut;

            if (formParams == null)
                return cmd;

            SqlParameter param;

            foreach (string key in formParams.Keys)
            {
                string columnName = key;
                string paramName = columnName.Replace("@p", "");
                object obj = formParams[key];

                SqlDbType columnType = DotNetTypeName2SqlDbType(obj.GetType());

                try
                {
                    param = new SqlParameter();

                    param.ParameterName = "@p" + columnName;
                    param.Value = obj;
                    param.SqlDbType = columnType;
                    param.Direction = ParameterDirection.InputOutput;
                    cmd.Parameters.Add(param);
                }
                catch
                {
                    continue;
                }
            }

            return cmd;
        }

        /// <summary>
        /// 数据类型名转换成SqlDbType
        /// </summary>
        /// <param name="sqlTypeName"></param>
        /// <returns></returns>
        private SqlDbType DotNetTypeName2SqlDbType(Type valueType)
        {
            SqlDbType dbType = SqlDbType.VarChar;
            //默认为Object

            if (valueType == typeof(int) || valueType == typeof(short))
            {
                dbType = SqlDbType.Int;
            }
            else if (valueType == typeof(long))
            {
                dbType = SqlDbType.BigInt;
            }
            else if (valueType == typeof(string))
            {
                dbType = SqlDbType.VarChar;
            }
            else if (valueType == typeof(bool))
            {
                dbType = SqlDbType.Bit;
            }
            else if (valueType == typeof(DateTime))
            {
                dbType = SqlDbType.DateTime;
            }
            else if (valueType == typeof(decimal))
            {
                dbType = SqlDbType.Decimal;
            }
            else if (valueType == typeof(float))
            {
                dbType = SqlDbType.Float;
            }
            else if (valueType == typeof(byte[]))
            {
                dbType = SqlDbType.Image;
            }

            return dbType;
        }

        #endregion
    }
}